// utils/sqlite.js
/**
 * SQLite 数据库操作封装（基于 plus.sqlite）
 * 支持高级查询条件（如 __gt, __in, __between, __like 等）
 * 方法命名规范：save, update, updateById, delete, deleteById, getOne, getOneById, getList, getPage
 */

// ==================== 工具函数 ====================

const DB_FILE_NAME = 'FillUp.db';
const DB_PATH = `_doc/${DB_FILE_NAME}`;

/**
 * 复制数据库文件（从 PRIVATE_WWW/FillUp.db 到 PRIVATE_DOC/）
 */
function copyDB() {
	return new Promise((resolve, reject) => {
		plus.io.requestFileSystem(plus.io.PRIVATE_WWW, fs => {
			fs.root.getFile("static/db/" + DB_FILE_NAME, { create: false }, fileEntry => {
				plus.io.requestFileSystem(plus.io.PRIVATE_DOC, fs1 => {
					fileEntry.copyTo(fs1.root, DB_FILE_NAME, entry => {
						resolve(entry.fullPath);
					}, reject);
				});
			});
		});
	});
}

/**
 * 检查数据库文件是否存在（在 _doc/ 下）
 */
function checkDB() {
	return new Promise((resolve, reject) => {
		plus.io.resolveLocalFileSystemURL(DB_PATH, entry => {
			resolve(entry.fullPath);
		}, () => {
			reject(false);
		});
	});
}

// ==================== 数据库服务类 ====================

const DB = {
	// 默认数据库配置
	dbName: 'FillUp',
	dbPath: DB_PATH,

	/**
	 * 安全转义值，用于 SQL 字符串拼接
	 * - 字符串：转义单引号并加引号
	 * - null/undefined → 'NULL'
	 * - boolean → 1/0
	 * - number → 直接返回
	 */
	_escape(val) {
		if (val == null) return 'NULL';
		if (typeof val === 'string') {
			return `'${val.replace(/'/g, "''")}'`; // 转义单引号
		}
		if (typeof val === 'boolean') return val ? '1' : '0';
		return val; // number 等原样返回
	},

	/**
	 * 构建 WHERE 子句（支持高级查询条件）
	 * @param {Object} where - 精确/范围/集合等条件，如 { age__gt: 18, id__in: [1,2,3] }
	 * @param {Object} like - 模糊查询条件，如 { name: '张' }
	 * @returns {string} 拼接好的 WHERE 子句（含 " WHERE " 前缀，或空字符串）
	 */
	_buildWhere(where = {}, like = {}) {
		const andClauses = [];
		const orClauses = [];

		// --- 处理 where 条件 ---
		for (let key in where) {
			let value = where[key];
			let field = key;
			let clause = '';

			// 支持 __or 后缀（用于 OR 条件）
			if (key.endsWith('__or')) {
				field = key.slice(0, -4);
				clause = `${field} = ${this._escape(value)}`;
				orClauses.push(clause);
				continue;
			}

			// 范围查询
			if (key.endsWith('__gt')) field = key.slice(0, -4), clause = `${field} > ${this._escape(value)}`;
			else if (key.endsWith('__lt')) field = key.slice(0, -4), clause = `${field} < ${this._escape(value)}`;
			else if (key.endsWith('__ge')) field = key.slice(0, -4), clause = `${field} >= ${this._escape(value)}`;
			else if (key.endsWith('__le')) field = key.slice(0, -4), clause = `${field} <= ${this._escape(value)}`;
			else if (key.endsWith('__ne')) field = key.slice(0, -4), clause = `${field} != ${this._escape(value)}`;
			// 集合查询
			else if (key.endsWith('__in') && Array.isArray(value)) {
				field = key.slice(0, -4);
				const vals = value.map(v => this._escape(v)).join(', ');
				clause = `${field} IN (${vals})`;
			} else if (key.endsWith('__notin') && Array.isArray(value)) {
				field = key.slice(0, -4);
				const vals = value.map(v => this._escape(v)).join(', ');
				clause = `${field} NOT IN (${vals})`;
			}
			// 区间查询（自动补全时间）
			else if (key.endsWith('__between') && Array.isArray(value) && value.length === 2) {
				field = key.slice(0, -9);
				let [start, end] = value;
				if (typeof start === 'string' && /^\d{4}-\d{2}-\d{2}$/.test(start)) start += ' 00:00:00';
				if (typeof end === 'string' && /^\d{4}-\d{2}-\d{2}$/.test(end)) end += ' 23:59:59';
				clause = `${field} BETWEEN ${this._escape(start)} AND ${this._escape(end)}`;
			}
			// 空值判断
			else if (key.endsWith('__isnull')) {
				field = key.slice(0, -8);
				clause = value ? `${field} IS NULL` : `${field} IS NOT NULL`;
			}
			// 默认精确匹配
			else {
				clause = `${key} = ${this._escape(value)}`;
			}

			andClauses.push(clause);
		}

		// --- 处理 like 模糊查询 ---
		for (let key in like) {
			let value = like[key];
			if (key.endsWith('__or')) {
				const field = key.slice(0, -4);
				orClauses.push(`${field} LIKE ${this._escape(`%${value}%`)}`);
			} else {
				andClauses.push(`${key} LIKE ${this._escape(`%${value}%`)}`);
			}
		}

		// --- 拼接最终 WHERE ---
		let whereClause = '';
		if (orClauses.length > 0) {
			whereClause += `(${orClauses.join(' OR ')})`;
			if (andClauses.length > 0) whereClause += ' AND ';
		}
		if (andClauses.length > 0) {
			whereClause += andClauses.join(' AND ');
		}

		return whereClause ? ` WHERE ${whereClause}` : '';
	},

	// ==================== 数据库连接管理 ====================

	/**
	 * 打开数据库
	 */
	open() {
		return new Promise((resolve, reject) => {
			plus.sqlite.openDatabase({
				name: this.dbName,
				path: this.dbPath,
				success: resolve,
				fail: reject
			});
		});
	},

	/**
	 * 关闭数据库
	 */
	close() {
		return new Promise((resolve, reject) => {
			plus.sqlite.closeDatabase({ name: this.dbName, success: resolve, fail: reject });
		});
	},

	/**
	 * 检查数据库是否已打开
	 */
	isOpen() {
		return plus.sqlite.isOpenDatabase({
			name: this.dbName,
			path: this.dbPath
		});
	},

	// ==================== 数据操作 ====================

	/**
	 * 插入单条记录
	 * @param {string} table - 表名
	 * @param {Object} data - 数据对象 {列: 值}
	 * @returns {Promise} 成功返回结果（无 ID 返回，如需可扩展）
	 */
	save(table, data) {
		const keys = Object.keys(data);
		const values = keys.map(k => this._escape(data[k]));
		const sql = `INSERT INTO ${table} (${keys.join(', ')}) VALUES(${values.join(', ')})`;
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({ name: this.dbName, sql, success: resolve, fail: reject });
		});
	},

	/**
	 * 按条件更新记录
	 * @param {string} table - 表名
	 * @param {Object} data - 要更新的数据 {列: 值}
	 * @param {Object} where - 更新条件（支持高级语法）
	 */
	update(table, data, where = {}) {
		const setClause = Object.keys(data).map(k => `${k} = ${this._escape(data[k])}`).join(', ');
		const whereClause = this._buildWhere(where);
		const sql = `UPDATE ${table} SET ${setClause}${whereClause}`;
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({ name: this.dbName, sql, success: resolve, fail: reject });
		});
	},

	/**
	 * 按 ID 更新
	 */
	updateById(table, data, id) {
		return this.update(table, data, { id });
	},

	/**
	 * 按条件删除记录
	 */
	delete(table, where = {}, like = {}) {
		const whereClause = this._buildWhere(where, like);
		const sql = `DELETE FROM ${table}${whereClause}`;
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({ name: this.dbName, sql, success: resolve, fail: reject });
		});
	},

	/**
	 * 按 ID 删除
	 */
	deleteById(table, id) {
		return this.delete(table, { id });
	},

	/**
	 * 查询单条记录
	 * @param {string} table - 表名
	 * @param {Object} where - 精确/高级条件
	 * @param {Object} like - 模糊条件
	 * @returns {Promise<Object|null>} 单条记录或 null
	 */
	getOne(table, where = {}, like = {}) {
		const whereClause = this._buildWhere(where, like);
		const sql = `SELECT * FROM ${table}${whereClause} LIMIT 1`;
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: this.dbName,
				sql,
				success: res => resolve(res[0] || null),
				fail: reject
			});
		});
	},

	/**
	 * 按 ID 查询单条
	 */
	getOneById(table, id) {
		return this.getOne(table, { id });
	},

	/**
	 * 查询列表（无分页）
	 * @param {string} orderBy - 排序字段，如 "id DESC"
	 */
	getList(table, where = {}, like = {}, orderBy = '') {
		const whereClause = this._buildWhere(where, like);
		let sql = `SELECT * FROM ${table}${whereClause}`;
		if (orderBy) sql += ` ORDER BY ${orderBy}`;
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({ name: this.dbName, sql, success: resolve, fail: reject });
		});
	},

	/**
	 * 分页查询
	 * @param {Object} options - { page, size, where, like, sort, order }
	 */
	getPage(table, { page = 1, size = 10, where = {}, like = {}, sort = 'id', order = 'DESC' } = {}) {
		const whereClause = this._buildWhere(where, like);
		let sql = `SELECT * FROM ${table}${whereClause} ORDER BY ${sort} ${order}`;
		const offset = (page - 1) * size;
		sql += ` LIMIT ${size} OFFSET ${offset}`;
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({ name: this.dbName, sql, success: resolve, fail: reject });
		});
	},

	/**
	 * 统计记录数
	 */
	count(table, where = {}, like = {}) {
		const whereClause = this._buildWhere(where, like);
		const sql = `SELECT COUNT(*) as total FROM ${table}${whereClause}`;
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: this.dbName,
				sql,
				success: res => resolve(res[0]?.total || 0),
				fail: reject
			});
		});
	},
};

// ==================== 导出 ====================

export { copyDB, checkDB };
export default DB;